﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using ClosedXML.Excel;
using System.IO;
using System.Collections;

namespace BusinessLayer.Logic.ReportProducer
{
    public class ExcelUtentiProfili
    {
        IXLWorksheet elencoRisorseSheet;
        XLWorkbook wb;

        int elencoRisorseRow = 2;
        
        private readonly int elencoRisorseUsernameColumn = 2;
        private readonly int elencoRisorseIdColumn = 1;
        private readonly int elencoRisorseNomeCognomeColumn = 3;
        private readonly int rigaHeader = 2;
        private readonly int startColonnaDati = 3;
        private readonly int colonnaNomiProfili = 2;
        private readonly int startRigaProfili = 3;
        private readonly XLColor arancione = XLColor.FromArgb(250, 191, 143);
        private readonly XLColor verdeChiaro = XLColor.FromArgb(179, 235, 179);
        private readonly XLColor verdeScuro = XLColor.FromArgb(0, 176, 80);
        private readonly XLColor headerColor = XLColor.FromArgb(255, 192, 0);
        private void scriviElencoRisorse( String id, String Username, String NomeCognome)
        {
            elencoRisorseSheet.Cell(elencoRisorseRow, elencoRisorseIdColumn).Value = id;
            elencoRisorseSheet.Cell(elencoRisorseRow, elencoRisorseUsernameColumn).Value = Username;
            elencoRisorseSheet.Cell(elencoRisorseRow, elencoRisorseNomeCognomeColumn).Value = NomeCognome;
            elencoRisorseRow++;
        }


        private void scriviTabella(bool scriviRiassunto,bool onlyColor,IEnumerable profili,IXLWorksheet sheet)
        {
            int colonnaDati = startColonnaDati;
            int currentRow=startRigaProfili;
            int utenteId = 1;
            foreach (UtenteProfiliDTO profiloUtente in profili)
            {
                if(scriviRiassunto)
                  scriviElencoRisorse(utenteId + "", profiloUtente.utente.Username, profiloUtente.utente.Cognome + " " + profiloUtente.utente.Nome);
                IXLCell headerCell = sheet.Cell(rigaHeader, colonnaDati);
                headerCell.Value = utenteId;
                headerCell.Style.Fill.BackgroundColor = headerColor;
                headerCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                headerCell.Style.Border.OutsideBorderColor = XLColor.Black;
                currentRow = startRigaProfili;
                foreach (ProfiliDTO profilo in profiloUtente.profili)
                {

                    sheet.Cell(currentRow, colonnaNomiProfili).Value = profilo.NomeProfilo;

                    int percentuale = (int)profilo.PercentualeProssimita;
                    IXLCell cell = sheet.Cell(currentRow, colonnaDati);
                    if (!onlyColor)
                    {
                        cell.Value = percentuale;
                        
                    }
                    XLColor color = XLColor.White;
                    if (percentuale >= 75)
                        color = verdeScuro;
                    else if (percentuale >= 65)
                        color = verdeChiaro;
                    else if (percentuale >= 55)
                        color = arancione;
                    cell.Style.Fill.BackgroundColor = color;
                    cell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
                    if(onlyColor)
                        cell.Style.Border.OutsideBorderColor = XLColor.White;
                    else
                        cell.Style.Border.OutsideBorderColor = XLColor.Black;
                    
                    currentRow++;

                }

                

                colonnaDati++;
                utenteId++;
            }



            IXLRange columndNomiProfili = sheet.Range(startRigaProfili, colonnaNomiProfili, currentRow - 1, colonnaNomiProfili);
            columndNomiProfili.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
            columndNomiProfili.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
            columndNomiProfili.Style.Border.InsideBorderColor = XLColor.Black;
            columndNomiProfili.Style.Border.OutsideBorderColor = XLColor.Black;
            if (onlyColor)
            {
               IXLRange range=sheet.Range(startRigaProfili, startColonnaDati, currentRow - 1, colonnaDati - 1);
               range.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
               range.Style.Border.OutsideBorderColor = XLColor.Black;
            }
            sheet.ColumnsUsed().AdjustToContents(rigaHeader, currentRow - 1);

            IXLRange legend =sheet.Range(currentRow + 1, 1, currentRow + 1, 5).Merge();
            legend.Value="Con profili certificabili";
            legend.Style.Fill.BackgroundColor = verdeScuro;
            legend.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
            legend = sheet.Range(currentRow + 2, 1, currentRow + 2, 5).Merge();
            legend.Value = "Con profili in larga parta coperti";
            legend.Style.Fill.BackgroundColor = verdeChiaro;
            legend.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
            legend = sheet.Range(currentRow + 3, 1, currentRow + 3, 5).Merge();
            legend.Value = "Con profili parzialmente coperti";
            legend.Style.Fill.BackgroundColor = arancione;
            legend.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;
        }

        public byte[] getExcelUtentiProfili(List<DTOUtente> utenti)
        {
            List < UtenteProfiliDTO > profili= new ProfiloLogic().calcolaProfili(utenti,false);
           wb = new XLWorkbook();
            elencoRisorseSheet = wb.Worksheets.Add("Elenco Risorse");
            IXLWorksheet perCodiceSheet = wb.Worksheets.Add("x codice");
            IXLWorksheet perValori = wb.Worksheets.Add("x valori");
            IXLWorksheet visionElective = wb.Worksheets.Add("vision elective");
            

            scriviElencoRisorse("Id","Username","Cognome Nome");
            var rngData = elencoRisorseSheet.FirstRowUsed().CellsUsed();
            rngData.Style.Border.BottomBorder=XLBorderStyleValues.Thin;


            scriviTabella(true,false, profili, perCodiceSheet);
            IEnumerable profiliOrdered = profili.OrderByDescending(x => x.profili.Sum(y => y.PercentualeProssimita));
            scriviTabella(false,false, profiliOrdered, perValori);
            scriviTabella(false,true, profiliOrdered, visionElective);



           elencoRisorseSheet.ColumnsUsed().AdjustToContents();


             MemoryStream ms=new MemoryStream();
             wb.SaveAs(ms);
           
            byte[] ret = ms.ToArray();
            ms.Close();
            return ret;
        }
    }
}
